using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    /// <summary> 
    ///Author: daiduong19051986@gmail.com
    /// <summary>
    public class TVC_LOOKUPDataAccess : Connection
    {
        #region Local Variable
        private string _strSPInsertName = "dbo.[procTVC_LOOKUPPRV_add]";
        private string _strSPUpdateName = "dbo.[procTVC_LOOKUPPRV_update]";
        private string _strSPDeleteName = "dbo.[procTVC_LOOKUPPRV_delete]";
        private string _strSPGetName = "dbo.[procTVC_LOOKUPPRV_get]";
        private string _strSPGetAllName = "dbo.[procTVC_LOOKUPPRV_getall]";
        private string _strSPGetPages = "dbo.[procTVC_LOOKUPPRV_getpaged]";
        private string _strSPIsExist = "dbo.[procTVC_LOOKUPPRV_isexist]";
        private string _strTableName = "[TVC_LOOKUPPRV]";
        private string _strSPGetTransferOutName = "dbo.[procTVC_LOOKUPPRV_gettransferout]";
        private string _strSPGetFilter = "dbo.[procTVC_LOOKUPPRV_getFilter]";
        #endregion Local Variable

        #region Method
        public TVC_LOOKUPInfo Get(string db, String TB, String TBField, String Code, ref string sErr)
        {
            string sql = @"SELECT        TB, TBField, Code, Lookup, Description, Status
            FROM            TVC_LOOKUP" + db + @"
            WHERE        (TB = @TB) AND (TBField = @TBField) AND (Code = @Code)";
            TVC_LOOKUPInfo objEntr = new TVC_LOOKUPInfo();
            InitConnect();
            InitCommand(sql);
            AddParameter(TVC_LOOKUPInfo.Field.TB.ToString(), TB);
            AddParameter(TVC_LOOKUPInfo.Field.TBField.ToString(), TBField);
            AddParameter(TVC_LOOKUPInfo.Field.Code.ToString(), Code);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }

            if (list.Rows.Count > 0)
                objEntr = new TVC_LOOKUPInfo(list.Rows[0]);
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            if (sErr != "") ErrorLog.SetLog(sErr);
            return objEntr;
        }

        public DataTable GetAll(string db, ref string sErr)
        {
            string sql = @"SELECT        TB, TBField, Code, Lookup, Description, Status
            FROM            TVC_LOOKUP" + db;
            InitConnect();
            InitCommand(sql);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        /// <summary>
        /// Return 1: Table is exist Identity Field
        /// Return 0: Table is not exist Identity Field
        /// Return -1: Erro
        /// </summary>
        /// <param name="tableName"></param>
        public Int32 Add(string db, TVC_LOOKUPInfo objEntr, ref string sErr)
        {
            string sql = @"INSERT INTO TVC_LOOKUP" + db + @"
                         (TB, TBField, Code, Lookup, Description, Status)
            VALUES        (@TB,@TBField,@Code,@Lookup,@Description,@Status)";
            int ret = -1;
            openConnection();
            InitCommand(sql);
            AddParameter(TVC_LOOKUPInfo.Field.TB.ToString(), objEntr.TB);
            AddParameter(TVC_LOOKUPInfo.Field.TBField.ToString(), objEntr.TBField);
            AddParameter(TVC_LOOKUPInfo.Field.Code.ToString(), objEntr.Code);
            AddParameter(TVC_LOOKUPInfo.Field.Lookup.ToString(), objEntr.Lookup);
            AddParameter(TVC_LOOKUPInfo.Field.Description.ToString(), objEntr.Description);
            AddParameter(TVC_LOOKUPInfo.Field.Status.ToString(), objEntr.Status);

            try
            {
                //command.ExecuteNonQuery();
                object tmp = executeScalar();
                if (tmp != null && tmp != DBNull.Value)
                    ret = Convert.ToInt32(tmp);
                else
                    ret = 0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }

        public string Update(string db, TVC_LOOKUPInfo objEntr)
        {
            string sql = @"UPDATE       TVC_LOOKUP" + db + @"
            SET                Lookup = @Lookup, Description = @Description, Status = @Status
            WHERE        (TB = @TB) AND (TBField = @TBField) AND (Code = @Code)";
            openConnection();
            InitCommand(sql);
            AddParameter(TVC_LOOKUPInfo.Field.TB.ToString(), objEntr.TB);
            AddParameter(TVC_LOOKUPInfo.Field.TBField.ToString(), objEntr.TBField);
            AddParameter(TVC_LOOKUPInfo.Field.Code.ToString(), objEntr.Code);
            AddParameter(TVC_LOOKUPInfo.Field.Lookup.ToString(), objEntr.Lookup);
            AddParameter(TVC_LOOKUPInfo.Field.Description.ToString(), objEntr.Description);
            AddParameter(TVC_LOOKUPInfo.Field.Status.ToString(), objEntr.Status);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public string Delete(string db, String TB, String TBField, String Code)
        {
            string sql = @"DELETE FROM TVC_LOOKUP" + db + @"
            WHERE        (TB = @TB) AND (TBField = @TBField) AND (Code = @Code)";
            openConnection();
            InitCommand(sql);
            AddParameter(TVC_LOOKUPInfo.Field.TB.ToString(), TB);
            AddParameter(TVC_LOOKUPInfo.Field.TBField.ToString(), TBField);
            AddParameter(TVC_LOOKUPInfo.Field.Code.ToString(), Code);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }



        public Boolean IsExist(string db, String TB, String TBField, String Code)
        {
            string sql = @"SELECT        TOP (1) Code
            FROM            TVC_LOOKUP" + db + @"
            WHERE        (TB = @TB) AND (TBField = @TBField) AND (Code = @Code)";
            InitConnect();
            InitCommand(sql);
            AddParameter(TVC_LOOKUPInfo.Field.TB.ToString(), TB);
            AddParameter(TVC_LOOKUPInfo.Field.TBField.ToString(), TBField);
            AddParameter(TVC_LOOKUPInfo.Field.Code.ToString(), Code);

            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);
            if (list.Rows.Count == 1)
                return true;
            return false;
        }

        private string CreateWhereClause(TVC_LOOKUPInfo obj)
        {
            String result = "";

            return result;
        }

        public DataTable Search(string db, string columnName, string columnValue, string condition, string tableName, ref string sErr)
        {
            string query = "select * from " + _strTableName + " where " + columnName + " " + condition + " " + columnValue;
            DataTable list = new DataTable();
            openConnection();
            try
            {
                list = executeSelectQuery(query);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            //    if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        public DataTable GetTransferOut(string dtb, object from, object to, ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGetTransferOutName);
            AddParameter("DB", dtb);
            AddParameter("FROM", from);
            AddParameter("TO", to);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        #endregion Method


        public DataTable Get(string db, string strFilter, string TBField, ref string sErr)
        {
            string sql = @"SELECT        LTRIM(RTRIM(Code)) AS Code, TB, TBField, Lookup, Description, Status, LTRIM(RTRIM(Code)) + ' - ' + Description AS displaycbo
            FROM            TVC_LOOKUP" + db + @"
            WHERE 1 = 1";
            if (!string.IsNullOrEmpty(TBField)) sql += " AND TBField = @TBField";
            if (!string.IsNullOrEmpty(strFilter)) sql += " AND " + strFilter;
            InitConnect();
            InitCommand(sql);
            if (!string.IsNullOrEmpty(TBField)) AddParameter("TBField", TBField);


            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }


            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }


        public DataTable GetQuery(string query, ref string sErr)
        {
            openConnection();

            DataTable list = new DataTable();
            try
            {
                list = executeSelectQuery(query);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }


            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public DataTable GetDescription(string db, string Table, string TBField, string Code, ref string sErr)
        {
            string sql = @"select top 1 [Description] from TVC.dbo.TVC_LOOKUP" + db + @"
                WHERE     (TB = @TB AND TBField = @TBField AND [Code] = @Code)";

            InitConnect();
            InitCommand(sql, CommandType.Text);
            AddParameter(TVC_LOOKUPInfo.Field.TB.ToString(), Table);
            AddParameter(TVC_LOOKUPInfo.Field.TBField.ToString(), TBField);
            AddParameter(TVC_LOOKUPInfo.Field.Code.ToString(), Code);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public DataTable GetTable(string db, string Table, string TBField, ref string sErr)
        {
            string sql = @"SELECT TB, TBField, Code, Lookup, Description, Status
                        FROM TVC_LOOKUP" + db + @"
                WHERE     (TB = @TB AND TBField = @TBField ) ";

            InitConnect();
            InitCommand(sql, CommandType.Text);
            AddParameter(TVC_LOOKUPInfo.Field.TB.ToString(), Table);
            AddParameter(TVC_LOOKUPInfo.Field.TBField.ToString(), TBField);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
    }
}
